SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SaveQuestionnaireVersion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SaveQuestionnaireVersion]
GO

/***************************************************************************
* Name:			SaveQuestionnaireVersion
* Purpose:		Insert/Update a questionnaire version
*
* PARAMETERS
* Name			Description					
* -------------		-----------------------------------------------------------
* @id			unique id (0 for new records to be inserted)
* @questionnaireID	id of the questionnaire to which the version belongs
* @active		indicates whether or not the version is active
* @description		information that describes the questionnaire version
* @published		indicates whether or not the version is published
* @version		the version no

***************************************************************************/
CREATE PROCEDURE [dbo].[SaveQuestionnaireVersion] (

@id INT,
@questionnaireID INT,
@active bit,
@description varchar(250),
@published bit,
@version INT,
@clone bit = true
)

AS

BEGIN
	IF @id = 0
		BEGIN
			DECLARE @oldVersion AS INT 
			DECLARE @oldVersionId AS INT 
			DECLARE @newVersionId AS INT 
			
			SELECT @oldVersion = ISNULL(max(Version), 0)
					from QuestionnaireVersion QV WHERE QV.QuestionnaireID = @questionnaireID
			
			IF @oldVersion > 0
			BEGIN
				SELECT @oldVersionId = [Id]
				FROM QuestionnaireVersion QV 
				WHERE QV.Version = @oldVersion AND QV.QuestionnaireID = @questionnaireID
			END
			

			INSERT INTO QuestionnaireVersion   	
				(Version,
				Active,
				QuestionnaireID,
				[Description],
				Published)
			VALUES
				(@version,
				 @active,
				 @questionnaireID,
				 @description,
				 @published )	

			IF @@ERROR <> 0 GOTO ERROR_HANDLER

			SELECT @newVersionId = IDENT_CURRENT('QuestionnaireVersion')
			
			IF @oldVersion > 0 AND @clone = 1
			BEGIN
				Exec CloneQuestionnaireVersion @newVersionId, @oldVersionId
			END
			
			IF @@ERROR <> 0 GOTO ERROR_HANDLER
			
			SELECT IDENT_CURRENT('QuestionnaireVersion')
			RETURN 

		END
	ELSE
	
		BEGIN
			if (@active = 1)
				SET @published = 1
			UPDATE QuestionnaireVersion
			SET
			Active		=	@active,
			QuestionnaireID = 	@questionnaireID,
			[Description]	=	@description,
			Published	=	@published
			WHERE ID = @id

			IF @@ERROR <> 0 GOTO ERROR_HANDLER

			SELECT @id	
			RETURN

		END				

	ERROR_HANDLER:
		RAISERROR('Unable to save QuestionnaireVersion', 16, 1)
		RETURN
END

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

